﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace kpi.Account
{
    public partial class UserRolesMgr : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Label_DebugMsg.Visible = false;
            TextBox_DebugMsg.Visible = false;

            if (!Page.IsPostBack)
            {
                //GridView_UserInRoles.DataSource = BuildGridView_UserRoles();
                //GridView_UserInRoles.DataBind();

                DropDownList_ShowRoleNames();
            }

            if (User.IsInRole("master"))
            {
                Button_DeleteRole.Visible = true;
                Label_CreateRole.Visible = true;
                TextBox_NewRoleName.Visible = true;
                Button_CreateRole.Visible = true;
            }
            else if (User.IsInRole("u_manager"))
            {
                Label_UserGuide.Text = "<br />";
                Label_UserGuide.Text += "<fieldset>";
                Label_UserGuide.Text += "<legend>说明</legend>";
                Label_UserGuide.Text += "<ol>";
                Label_UserGuide.Text += "<li>请将需要对下级进行考评的账户（管理者账户）加入“u_manager”账户组。</li>";
                Label_UserGuide.Text += "<li>系统中的账户只分为“管理者账户”和“一般账户”两种，区别是“管理者账户”可以对所管理的“一般账户”进行查看和评分。</li>";
                Label_UserGuide.Text += "<li>所有账户注册完成即为“一般账户”，“管理者账户”需要系统管理员进行<a href=\"UserRolesMgr.aspx\">人工指定</a>。</li>";
                Label_UserGuide.Text += "</ol>";
                Label_UserGuide.Text += "</fieldset>";
                Label_UserGuide.Visible = true;
            }            
        }

        protected String GetSqlConnectionString()
        {
            return "Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA";
        }
        
        protected SqlConnection GetSqlConnection(String SqlConnectionString)
        {
            if (SqlConnectionString == null)
            {
                SqlConnectionString = "Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA";
            }
            SqlConnection SqlConnection = new SqlConnection(SqlConnectionString);

            return SqlConnection;
        }

        protected void DropDownList_ShowRoleNames()
        {
            DropDownList_RoleName.Dispose();

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT RoleId, RoleName FROM [dbo].[aspnet_Roles] WHERE RoleName <> 'master' ORDER BY RoleName";
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader RoleNameReader = SelectCommand.ExecuteReader();

            while (RoleNameReader.Read())
            {
                DropDownList_RoleName.Items.Add(new ListItem(RoleNameReader["RoleName"].ToString(), RoleNameReader["RoleId"].ToString()));
            }

            RoleNameReader.Dispose();
            RoleNameReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();
        }

        protected void ShowListBox_IncUserName(String RoleId)
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommandString = "SELECT UserName, UserId FROM [dbo].[aspnet_Users] WHERE UserId IN (SELECT UserId FROM [dbo].[aspnet_UsersInRoles] WHERE RoleId=\'" + RoleId + "\') ORDER BY UserName";
            SqlCommand SelectCommand = new SqlCommand(selectCommandString, SqlConnection);
            SqlDataReader IncUserNameReader = SelectCommand.ExecuteReader();

            while (IncUserNameReader.Read())
            {
                ListBox_IncUserName.Items.Add(new ListItem(IncUserNameReader["UserName"].ToString(), IncUserNameReader["UserId"].ToString()));
            }

            IncUserNameReader.Dispose();
            IncUserNameReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();
        }

        protected void ShowListBox_ExtUserName(String RoleId)
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT UserName, UserId FROM [dbo].[aspnet_Users] WHERE UserId NOT IN (SELECT UserId FROM [dbo].[aspnet_UsersInRoles] WHERE RoleId =\'" + RoleId + "\') AND UserName <> 'root' ORDER BY UserName";
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader ExtUserNameReader = SelectCommand.ExecuteReader();
           
            while (ExtUserNameReader.Read())
            {
                ListBox_ExtUserName.Items.Add(new ListItem(ExtUserNameReader["UserName"].ToString(), ExtUserNameReader["UserId"].ToString()));
            }

            ExtUserNameReader.Dispose();
            ExtUserNameReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();
        }

        protected DataSet BuildDropDownList_RoleNames()
        {
            DataSet RoleNameDataSet = new DataSet();
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            String selectCommand = "SELECT RoleName FROM [dbo].[aspnet_Roles] WHERE RoleName <> 'master'";

            //SqlConnection.Open();

            //SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand, SqlConnection);
            //dataAdapter.Fill(RoleNameDataSet,"RoleName");
            //dataAdapter.Dispose();

            //使用SqlDataReader效率更高
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader RoleNameReader = SelectCommand.ExecuteReader();

            while (RoleNameReader.Read())
            {

            }

            RoleNameReader.Dispose();
            RoleNameReader.Close();
            SelectCommand.Dispose();            
            SqlConnection.Close();
            return RoleNameDataSet;
        }

        protected DataSet BuildGridView_UserRoles()
        {

            DataSet UserRolesDataSet = new DataSet();

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT U.UserName, UIR.UserId, UIR.RoleId, R.RoleName FROM [dbo].[aspnet_Users] U,  [dbo].[aspnet_UsersInRoles] UIR, [dbo].[aspnet_Roles] R WHERE U.UserId = UIR.UserId AND R.RoleId = UIR.RoleId";

            SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand, SqlConnection);
            dataAdapter.Fill(UserRolesDataSet);
            dataAdapter.Dispose();

            SqlConnection.Close();

            return UserRolesDataSet;
        }

        protected void BuildGridViews_UserRoles()
        {

        }

        protected void Button_UserAddMgr_Click(object sender, EventArgs e)
        {
            //SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            //SqlConnection.Open();

            String UserNames = ListBox_ExtUserName.SelectedItem.Text;
            String UserId = ListBox_ExtUserName.SelectedValue;            

            String RoleNames = DropDownList_RoleName.SelectedItem.Text;
            String RoleId = DropDownList_RoleName.SelectedValue;
            
            foreach (ListItem UserNameItem in this.ListBox_ExtUserName.Items)
            {
                if (UserNameItem.Selected)
                {
                    //TextBox_DebugMsg.Text += UserIdItem.Value + "\r\n";

                    //留存一下，使用语句将用户加入组，可以实现多选一次加入
                    //新注册用户已经加入User组，此处应为UPDATE。
                    //String CommandString = "INSERT INTO [dbo].[aspnet_UsersInRoles] (UserId, RoleId) VALUES (@UserId, @RoleId)";
                    //String CommandString = "UPDATE [dbo].[aspnet_UsersInRoles] SET RoleId=@RoleId WHERE UserId=@UserId";
                    //SqlCommand InsertCommand = new SqlCommand(CommandString, SqlConnection);
                    //SqlParameter[0] = new SqlParameter("@UserId", SqlDbType.VarChar, 50);
                    //SqlParameter[1] = new SqlParameter("@RoleId", SqlDbType.VarChar, 50);
                    //SqlParameter[0].Value = UserIdItem.Value;
                    //SqlParameter[1].Value = RoleId;

                    //for (int i = 0; i < SqlParameter.Length; i++)
                    //{
                    //    InsertCommand.Parameters.Add(SqlParameter[i]);
                    //}
                    //InsertCommand.CommandType = CommandType.Text;
                    //InsertCommand.ExecuteNonQuery();

                    //TextBox_DebugMsg.Text += InsertCommand.ToString() + "\r\n";

                    //ExecuteUpdateQuery(item.Value);
                    //ListBox_UserMgr.Items.Insert(0,item);
                    //留存一下结束

                    //String UserNames = ListBox_ExtUserName.SelectedItem.ToString();
                    //String RoleNames = DropDownList_RoleName.SelectedItem.ToString();
                    
                    SQLProcedure_AddUsersToRoles(UserNameItem.ToString(), RoleNames);
                }
            }
            //SqlConnection.Close();

            ListBox_IncUserName.Items.Clear();
            ShowListBox_IncUserName(RoleId);           

            ListBox_ExtUserName.Items.Clear();
            ShowListBox_ExtUserName(RoleId);           
        }

        protected void SQLProcedure_AddUsersToRoles(String UserNames, String RoleNames)
        {
            //执行存储过程aspnet_UsersInRoles_AddUsersToRoles，将注册用户加入User组。

            SqlConnection sqlConnection = GetSqlConnection(null);
            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand("aspnet_UsersInRoles_AddUsersToRoles", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256);
            sqlCommand.Parameters.Add("@UserNames", SqlDbType.NVarChar, 4000);
            sqlCommand.Parameters.Add("@RoleNames", SqlDbType.NVarChar, 4000);
            sqlCommand.Parameters.Add("@CurrentTimeUtc", SqlDbType.DateTime);

            sqlCommand.Parameters["@ApplicationName"].Value = "kpi_forms";
            sqlCommand.Parameters["@UserNames"].Value = UserNames;
            sqlCommand.Parameters["@RoleNames"].Value = RoleNames;
            sqlCommand.Parameters["@CurrentTimeUtc"].Value = DateTime.Now;

            sqlCommand.ExecuteNonQuery();
            sqlCommand.Dispose();
            sqlConnection.Close();
        }

        protected void Button_UserRemMgr_Click(object sender, EventArgs e)
        {
            String UserNames = ListBox_IncUserName.SelectedItem.Text;
            String UserId = ListBox_ExtUserName.SelectedValue;

            String RoleNames = DropDownList_RoleName.SelectedItem.Text;
            String RoleId = DropDownList_RoleName.SelectedValue;

            foreach (ListItem UserNameItem in this.ListBox_IncUserName.Items)
            {
                if (UserNameItem.Selected)
                {
                    SQLProcedure_RemoveUsersFromRoles(UserNameItem.ToString(), RoleNames);
                }
            }            

            ListBox_IncUserName.Items.Clear();
            ShowListBox_IncUserName(RoleId);

            ListBox_ExtUserName.Items.Clear();
            ShowListBox_ExtUserName(RoleId);
        }

        protected void SQLProcedure_RemoveUsersFromRoles(String UserNames, String RoleNames)
        {            
            SqlConnection sqlConnection = GetSqlConnection(null);
            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand("aspnet_UsersInRoles_RemoveUsersFromRoles", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256);
            sqlCommand.Parameters.Add("@UserNames", SqlDbType.NVarChar, 4000);
            sqlCommand.Parameters.Add("@RoleNames", SqlDbType.NVarChar, 4000);

            sqlCommand.Parameters["@ApplicationName"].Value = "kpi_forms";
            sqlCommand.Parameters["@UserNames"].Value = UserNames;
            sqlCommand.Parameters["@RoleNames"].Value = RoleNames;            

            sqlCommand.ExecuteNonQuery();
            sqlCommand.Dispose();
            sqlConnection.Close();
        }

        protected void Button_UserRoleMgr_Click(object sender, EventArgs e)
        {
            Label_DebugMsg.Text = "";
            
            String UserGroupId = DropDownList_RoleName.SelectedValue.ToString();
            String UserGroupName = DropDownList_RoleName.SelectedItem.ToString();

            Label_DebugMsg.Text = "[" + UserGroupName + "] [" + UserGroupId + "]";

            Label_IncAcc.Text = UserGroupName + "组 <br />已经包含的用户";
            Label_IncAcc.Visible = true;
            Label_ExtAcc.Text = UserGroupName + "组 <br />未经包含的用户";
            Label_ExtAcc.Visible = true;

            ListBox_IncUserName.Items.Clear();
            ListBox_IncUserName.Text = "";
            ShowListBox_IncUserName(UserGroupId);
            ListBox_IncUserName.Visible = true;
            
            ListBox_ExtUserName.Items.Clear();
            ListBox_ExtUserName.Text = "";
            ShowListBox_ExtUserName(UserGroupId);
            ListBox_ExtUserName.Visible = true;

            Button_UserAddMgr.Visible = true;
            Button_UserRemMgr.Visible = true;

            //ListBox_ExtUserName.SelectedIndexChanged += new EventHandler(ListBox_ExtUserName_SelectedIndexChanged);
        }       

        protected void Button_CreateRole_Click(object sender, EventArgs e)
        {
            if (TextBox_NewRoleName.Text != null)
            {
                SQLProcedure_CreateRole(null, TextBox_NewRoleName.Text);
            }
            
            TextBox_NewRoleName.Text = "";

            DropDownList_RoleName.Items.Clear();
            DropDownList_ShowRoleNames();            
        }

        protected void SQLProcedure_CreateRole(String ApplicationName, String RoleName)
        {
            //执行存储过程aspnet_UsersInRoles_AddUsersToRoles，将注册用户加入User组。

            SqlConnection sqlConnection = GetSqlConnection(null);
            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand("aspnet_Roles_CreateRole", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256);            
            sqlCommand.Parameters.Add("@RoleName", SqlDbType.NVarChar, 4000);

            sqlCommand.Parameters["@ApplicationName"].Value = "kpi_forms";           
            sqlCommand.Parameters["@RoleName"].Value = RoleName;            

            sqlCommand.ExecuteNonQuery();
            sqlCommand.Dispose();
            sqlConnection.Close();
        }

        protected void Button_DeleteRole_Click(object sender, EventArgs e)
        {
            //调试信息
            //Label_DebugMsg.Text = "TEXT:" + DropDownList_RoleName.SelectedItem.ToString();
            //Label_DebugMsg.Visible = true;

            SQLProcedure_DeleteRole(null, DropDownList_RoleName.SelectedItem.ToString());

            DropDownList_RoleName.Items.Clear();
            DropDownList_ShowRoleNames();
        }

        protected void SQLProcedure_DeleteRole(String ApplicationName, String RoleName)
        {
            SqlConnection sqlConnection = GetSqlConnection(null);
            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand("aspnet_Roles_DeleteRole", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256);            
            sqlCommand.Parameters.Add("@RoleName", SqlDbType.NVarChar, 4000);
            sqlCommand.Parameters.Add("@DeleteOnlyIfRoleIsEmpty", SqlDbType.Bit);

            sqlCommand.Parameters["@ApplicationName"].Value = "kpi_forms";            
            sqlCommand.Parameters["@RoleName"].Value = RoleName;
            sqlCommand.Parameters["@DeleteOnlyIfRoleIsEmpty"].Value = 1;

            sqlCommand.ExecuteNonQuery();
            sqlCommand.Dispose();
            sqlConnection.Close();
        }       
    }
}